package com.yzycoc.qb78.util;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.yzycoc.qb78.service.Qb78Service;
import com.yzycoc.qb78.vo.DataQb;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.math.BigDecimal;
import java.nio.charset.Charset;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 创建时间： 2023/10/27
 *
 * @author yzy
 * @version 1.0
 **/
@Slf4j
public class ExcelUtil {

    public static final String filePath = System.getProperty("user.dir") + "//" + "temp.json";

    public String run(JSONObject bodyJSON) {
        String tempConfigJson = FileUtil.readUtf8String(filePath);
        if (StrUtil.isEmpty(tempConfigJson)) {
            tempConfigJson = "{}";
        }
        JSONObject tempConfig = JSONObject.parseObject(tempConfigJson);
        JSONArray jsonArray = bodyJSON.getJSONArray("rows");
        BigDecimal idConfig = tempConfig.getBigDecimal("id");
        idConfig = idConfig == null ? BigDecimal.ZERO : idConfig;
        List<DataQb> saveData = new ArrayList<>();
        for (int i = jsonArray.size() - 1; i >= 0; i--) {
            JSONObject data = jsonArray.getJSONObject(i);
            String idStr = data.getString("id");
            BigDecimal bigDecimal = new BigDecimal(idStr);
            //System.out.println(idStr);
            //System.out.println(idConfig);
            //System.out.println(bigDecimal.toString());
            //System.out.println("");
            int i1 = bigDecimal.compareTo(idConfig);
            if (bigDecimal.compareTo(idConfig) > 0) {
                idConfig = bigDecimal;
                //excel 保存并处理
                saveData.add(excel(data));
            }
        }//1717824038753144832 //1717824352667439104
        String val = "";
        tempConfig.put("id", idConfig);
        FileUtil.writeString(tempConfig.toJSONString(), filePath, Charset.forName("UTF-8"));
        if (saveData.size() > 0) {
            JSONObject config = Qb78Service.config;
            String time = DateUtil.format(new Date(), new SimpleDateFormat(config.getString("format")));
            String path = System.getProperty("user.dir") + "//excel//" + "聊天_" + time + ".xlsx";
            val = saveExcel(saveData, path);
            return "采集新数据" + saveData.size() + "条，共采集:" + val + "条";
        }
        return "未采集到新数据";
    }


    private String saveExcel(List<DataQb> saveData, String path) {
        Workbook workbook;
        Sheet sheet;
        File file = new File(path);
        if (!file.exists()) {
            // 如果文件不存在，新建一个Excel文件
            workbook = new XSSFWorkbook();
            CellStyle titleStyle = workbook.createCellStyle();
            // 设置背景色为蓝色
            titleStyle.setFillForegroundColor(IndexedColors.INDIGO.getIndex());
            titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
            // 设置字体颜色为白色
            Font font = workbook.createFont();
            font.setFontHeightInPoints((short) 13);
            font.setColor(IndexedColors.WHITE.getIndex());
            titleStyle.setFont(font);
            // 设置文本居中对齐
            titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
            titleStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);

            sheet = workbook.createSheet("Data");
            Row newRow = sheet.createRow(0);

            sheet.setColumnWidth(0, 14 * 256);
            sheet.setColumnWidth(1, 20 * 256);
            sheet.setColumnWidth(2, 10 * 256);
            sheet.setColumnWidth(3, 14 * 256);
            sheet.setColumnWidth(4, 14 * 256);
            sheet.setColumnWidth(5, 18 * 256);
            sheet.setColumnWidth(6, 9 * 256);
            sheet.setColumnWidth(7, 14 * 256);
            sheet.setColumnWidth(8, 14 * 256);
            sheet.setColumnWidth(9, 8 * 256);
            sheet.setColumnWidth(10, 20 * 256);
            newRow.createCell(10).setCellValue("添加时间");
            List<String> list = Arrays.asList("唯一id", "添加时间", "模型判断", "识别文本", "聊天记录", "游戏名", "区服ID", "用户ID", "角色名称", "状态", "抓取时间");
            for (int i = 0; i < list.size(); i++) {
                Cell cell = newRow.createCell(i);
                cell.setCellStyle(titleStyle);
                cell.setCellValue(list.get(i));
            }
        } else {
            // 如果文件已存在，打开原有的Excel文件
            try {
                InputStream fileOut = new FileInputStream(path);
                workbook = WorkbookFactory.create(fileOut);
                sheet = workbook.getSheetAt(0); // 获取第一个工作表
            } catch (Exception e) {
                e.printStackTrace();
                return "保存异常";
            }
        }
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        int lastRowNum = sheet.getLastRowNum(); // 获取最后一行的行号（0-based）

        CellStyle wechatTitleStyle = workbook.createCellStyle();
        // 设置背景色为蓝色
        wechatTitleStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        wechatTitleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        // 设置字体颜色为白色
        Font font = workbook.createFont();
        font.setColor(IndexedColors.WHITE.getIndex());
        wechatTitleStyle.setFont(font);
        // 设置文本居中对齐
        wechatTitleStyle.setAlignment(CellStyle.ALIGN_CENTER);
        wechatTitleStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);


        CellStyle QqTitleStyle = workbook.createCellStyle();
        // 设置背景色为
        QqTitleStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
        QqTitleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        QqTitleStyle.setFont(font);
        // 设置文本居中对齐
        QqTitleStyle.setAlignment(CellStyle.ALIGN_CENTER);
        QqTitleStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);

        CellStyle errorTitleStyle = workbook.createCellStyle();
        // 设置背景色为蓝色
        errorTitleStyle.setFillForegroundColor(IndexedColors.CORAL.getIndex());
        errorTitleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        errorTitleStyle.setFont(font);
        // 设置文本居中对齐
        errorTitleStyle.setAlignment(CellStyle.ALIGN_CENTER);
        errorTitleStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);

        CellStyle titleStyle = workbook.createCellStyle();
// 设置文本居中对齐
        titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
        titleStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
        for (int i = 0; i < saveData.size(); i++) {
            DataQb data = saveData.get(i);
            Row newRow = sheet.createRow(lastRowNum + 1 + i);
            newRow.createCell(0).setCellValue(String.valueOf(data.getId()));
            newRow.createCell(1).setCellValue(simpleDateFormat.format(data.getAddDate()));
            Cell cell = newRow.createCell(2);
            cell.setCellValue(data.getAi());
            if ("异常".equals(data.getAi())) {
                cell.setCellStyle(errorTitleStyle);
            } else if ("微信号".equals(data.getAi())) {
                cell.setCellStyle(wechatTitleStyle);
            } else if ("QQ".equals(data.getAi())) {
                cell.setCellStyle(QqTitleStyle);
            }else{
                cell.setCellStyle(titleStyle);
            }
            newRow.createCell(3).setCellValue(data.getAiTxt());
            newRow.createCell(4).setCellValue(data.getChatContents());
            newRow.createCell(5).setCellValue(data.getGameName());
            newRow.createCell(6).setCellValue(data.getServiceId());
            newRow.createCell(7).setCellValue(data.getUserId());
            newRow.createCell(8).setCellValue(data.getRoleName());
            newRow.createCell(9).setCellValue(data.getStatusName());
            newRow.createCell(10).setCellValue(simpleDateFormat.format(data.getDate()));
        }

        FileOutputStream outputStream = null;
        try {
            sheet.autoSizeColumn(0);
            outputStream = new FileOutputStream(path);
            workbook.write(outputStream);
        } catch (FileNotFoundException e) {
            if (e.getMessage().contains("另一个程序正在使用此文件")) {
                path = path.replace("聊天_", "聊天_1_");
                log.info("文件被占用，更换新路径存储：" + path);
                return saveExcel(saveData, path);
            } else {
                e.printStackTrace();
            }
        } catch (IOException e) {
            e.printStackTrace();
            return "写入失败";
        } finally {
            try {
                if (workbook != null) {
                    workbook.close();
                }

                if (outputStream != null) {
                    outputStream.flush();
                    outputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return lastRowNum + saveData.size() + "";
    }


    /**
     * excel 保存数据
     *
     * @param json
     */
    public DataQb excel(JSONObject json) {
        DataQb data = JSONObject.toJavaObject(json, DataQb.class);
        data.setDate(new Date());
        Long addTime = data.getAddTime();
        data.setAddDate(new Date(addTime));
        String statusName;
        switch (data.getOpStatus()) {
            case 0:
                statusName = "";
                break;
            case 1:
                statusName = "禁言";
                break;
            case -1:
                statusName = "操作失败";
                break;
            default:
                statusName = "未知:" + data.getOpStatus();
                break;
        }
        data.setStatusName(statusName);
        JSONObject config = Qb78Service.config;
        String wechat = matchWeChat(data.getChatContents(), "a-zA-Z0-9_" + config.getString("WX"));
        String ai = "未知";
        if (StrUtil.isNotEmpty(wechat)) {
            ai = "异常";
            String wxHeader = isFirstCharDigit(wechat, config.getString("WX_HEADER"));
            if (StrUtil.isNotEmpty(wxHeader)) {
                ai = "微信号";
            } else if (wechat.length() <= 11) {
                ai = "QQ";
            }
            data.setAiTxt(wechat);
        }
        data.setAi(ai);
        return data;
    }


    public static String matchWeChat(String text, String supplement) {

        String pattern = "[" + supplement + "]{5,20}";  // 匹配包含字母、数字、下划线和短横线的5到20个字符
        Pattern regex = Pattern.compile(pattern);
        Matcher matcher = regex.matcher(text);
        while (matcher.find()) {
            String match = matcher.group().toLowerCase();
            if (match.length() < 21) {
                return match;
            }
        }
        return null;
    }

    public static String isFirstCharDigit(String text, String supplement) {
        String pattern = "^[" + supplement + "]";
        Pattern regex = Pattern.compile(pattern);
        Matcher matcher = regex.matcher(text);
        while (matcher.find()) {
            String match = matcher.group().toLowerCase();
            return match;
        }
        return null;
    }


    /**
     * 保存数据存储
     *
     * @param saveData 数据
     */
    /*private String saveExcel(List<DataQb> saveData,String path) {
        File file = new File(path);
        if(file.exists()){
            // 这里 需要指定写用哪个class去写
            try (ExcelWriter excelWriter = EasyExcel.write(path, DataQb.class).build()) {
                List<DataQb> originalUserDataList = EasyExcel.read(file).head(DataQb.class).sheet().doReadSync();
                originalUserDataList.addAll(saveData);
                // 这里注意 如果同一个sheet只要创建一次
                WriteSheet writeSheet = EasyExcel.writerSheet("奇葩聊天").build();
                excelWriter.write(originalUserDataList, writeSheet);
                return originalUserDataList.size() + "";
            }catch (Exception e){
                e.printStackTrace();
                //文件不可写，被其他程序打开中
                path = path.replace("聊天_","聊天_error_");
                return saveExcel(saveData,path);
            }
            //try {
            //    List<DataQb> originalUserDataList = EasyExcel.read(file).head(DataQb.class).sheet().doReadSync();
            //    List<DataQb> mergedUserDataList = new ArrayList<>(originalUserDataList);
            //    mergedUserDataList.addAll(saveData);
            //    ExcelWriter excelWriter = EasyExcel.write(file).build();
            //    WriteSheet writeSheet = EasyExcel.writerSheet().build();
            //    excelWriter.write(mergedUserDataList, writeSheet);
            //    excelWriter.finish();
            //    return mergedUserDataList.size() + "";
            //}catch (Exception e){
            //}
        }else{
            EasyExcel.write(file,DataQb.class)
                    .sheet("奇葩聊天").doWrite(saveData);
        }
        return saveData.size() + "";
    }*/
}
